Set Auto Increment Starting Value {postgres}

들어가기 전에,

sequence 이름찾는 방법부터 알아보자.

SELECT pg_get_serial_sequence('image', 'imgId');

또는 모든 sequence를 찾고싶다면..

SELECT * FROM pg_sequences;

sequence 이름 찾는 방법 (GPT 응답)

Since your sequence is named public."image_imgId_seq", you can reconfigure the increment value using the following steps:

1. Update the Increment Value

To change the increment value (how much the sequence increases per new entry):

ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;

(Change 1 to any desired increment.)

2. Restart the Sequence with a Specific Value

If you want the next generated ID to start at 1000, run:

ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;

Alternatively, use:

SELECT setval('public."image_imgId_seq"', 1000, false);

• First argument (1000) → Sets the current value.
• Second argument (false) → Ensures the next nextval() call returns 1000, not 1001.

3. Verify the Change

Run:

SELECT nextval('public."image_imgId_seq"');

This should return 1000 (or your desired starting value).

4. Ensure the Sequence is Linked to Your Column

Make sure the image_imgId column uses the sequence:

SELECT column_default
FROM information_schema.columns
WHERE table_name = 'image' AND column_name = 'imgId';

If the output does not contain nextvalregclass, manually link the sequence:

ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');

5. Test with an Insert

INSERT INTO image DEFAULT VALUES RETURNING imgId;

This should return 1000 if everything is configured correctly.

Summary

✅ Set increment value: ALTER SEQUENCE public."image_imgId_seq" INCREMENT BY 1;
✅ Restart at 1000: ALTER SEQUENCE public."image_imgId_seq" RESTART WITH 1000;
✅ Manually set value: SELECT setval('public."image_imgId_seq"', 1000, false);
✅ Ensure the column uses the sequence: ALTER TABLE image ALTER COLUMN imgId SET DEFAULT nextval('public."image_imgId_seq"');
✅ Test with INSERT

Let me know if you need further help! 🚀